package dao;

import java.sql.*;
import java.sql.Date;
import java.util.*;
import java.io.*;


import model.*;

public class CSVImporter {

	private String file;

	public CSVImporter(String bestand) {
		file = bestand;
	}

	@SuppressWarnings("deprecation")
	public void importMetingen() {

		ArrayList<Meting> metingen = new ArrayList<Meting>();


		try {
			BufferedReader br = new BufferedReader(new FileReader(file));
			String strLine = "";
			StringTokenizer st = null;
			int lineNumber = 0;

			while ((strLine = br.readLine()) != null) {
				lineNumber++;
				st = new StringTokenizer(strLine, ",");

				int stationid = Integer.parseInt(st.nextToken().toString().trim());
				
				String strDate = st.nextToken().toString().trim();
				java.sql.Date datum = null;
				int year = Integer.parseInt(strDate.substring(0, 4));
				int month = Integer.parseInt(strDate.substring(4, 6));
				System.out.println(month);
				int day = Integer.parseInt(strDate.substring(6, 8));
				
				System.out.println(""+year+"-"+month+"-"+day);
				
				final Calendar cal = Calendar.getInstance();
				cal.set(year, month-1, day);
				datum = new Date(cal.getTimeInMillis());
				
				System.out.println(""+datum);
				
				int windrichting = Integer.parseInt(st.nextToken().toString().trim());
				int gemwindsnelheid = Integer.parseInt(st.nextToken().toString().trim());
				int maxwind = Integer.parseInt(st.nextToken().toString().trim());
				int minwind = Integer.parseInt(st.nextToken().toString().trim());
				int gemtemp = Integer.parseInt(st.nextToken().toString().trim());
				int maxtemp = Integer.parseInt(st.nextToken().toString().trim());
				int mintemp = Integer.parseInt(st.nextToken().toString().trim());
				int mintemp10 = Integer.parseInt(st.nextToken().toString().trim());
				int duurNeerslag = Integer.parseInt(st.nextToken().toString().trim());
				int somNeerslag = Integer.parseInt(st.nextToken().toString().trim());
				int gemDruk = Integer.parseInt(st.nextToken().toString().trim());
				int maxDruk = Integer.parseInt(st.nextToken().toString().trim());
				int minDruk = Integer.parseInt(st.nextToken().toString().trim());

				Meting meting = new Meting(stationid, datum, windrichting,
						gemwindsnelheid, maxwind, minwind, gemtemp, maxtemp,
						mintemp, mintemp10, gemDruk, maxDruk, minDruk,
						duurNeerslag, somNeerslag);

				metingen.add(meting);
			}

			Properties props = new Properties();
			FileInputStream in;
			try {
				in = new FileInputStream("database.properties");
				props.load(in);
				in.close();

				String driver = props.getProperty("jdbc.drivers");
			
				if (driver.equalsIgnoreCase("com.mysql.jdbc.Driver")) {
					mysqlInsert(metingen);
				} else if (driver.equalsIgnoreCase("org.postgresql.Driver")) {
					postgresqlInsert(metingen);
				} else if (driver
						.equalsIgnoreCase("org.apache.derby.jdbc.EmbeddedDriver")) {
					derbyInsert(metingen);
				}			
				
			System.out.println("Klaar met importeren");
			
			}
			catch( FileNotFoundException e)
			{
				e.printStackTrace();
			}
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	
	private void mysqlInsert(ArrayList<Meting> metingen) {

		String qInsertMetingen = "INSERT INTO metingen VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		
		String qCreateTableStation = 	"CREATE TABLE station" +
										"(" +
										"station_id INT NOT NULL," +
										"plaats VARCHAR(244)," +
										"positie VARCHAR(50)," +
										"terreinHoogte DOUBLE," +
										"omgeving VARCHAR(255)," +
										"grondsoort VARCHAR(255)," +
										"baroHoogte DOUBLE," +
										"windHoogte DOUBLE," +
										"CONSTRAINT pk_stationID PRIMARY KEY (station_id)" +
										") ENGINE=InnoDB";
		
		String qCreateTableMetingen = 	"CREATE TABLE metingen" +
										"(" +
										"station_id INT NOT NULL," +
										"datum Date NOT NULL," +
										"windrichting INT," +
										"gemWindsnelheid INT," +
										"maxWindsnelheid INT," +
										"minWindsnelheid INT," +
										"gemTemperatuur INT," +
										"maxTemperatuur INT," +
										"minTemperatuur INT," +
										"minTemperatuur10 INT," +
										"gemLuchtdruk INT," +
										"maxLuchtdruk INT," +
										"minLuchtdruk INT," +
										"duurNeerslag INT," +
										"somNeerslag INT," +
										"CONSTRAINT pk_station PRIMARY KEY (station_id, datum)" +
										") ENGINE=InnoDB";
		
		//String qCreateDatabase = "CREATE DATABASE weer";
		
		Connection conn = DBManager.getInstance().getConnection();
				
		//***************************************************************************************
		//***************************************************************************************
		//***************************************************************************************
		// Check if database exists
		// if not create database and tables
		
		
		boolean exists = false;
		
		if( exists == false ) {
			try {
				Statement st = conn.createStatement();
				//st.execute(qCreateDatabase);
				st.execute(qCreateTableStation);
				st.execute(qCreateTableMetingen);
				st.close();
			} 
			catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		//***************************************************************************************
		//***************************************************************************************
		//****************************************************************************************
		
		try {
			conn.setAutoCommit(false);

			PreparedStatement prst = conn.prepareStatement(qInsertMetingen);

			for (int i = 0; i < metingen.size(); i++) {
				int station_id = metingen.get(i).getStation_id();
				java.sql.Date datum = new Date(metingen.get(i).getDatum().getTime());
				
				System.out.println(""+datum);
				
				int windrichting = metingen.get(i).getWindrichting();
				int gemWindsnelheid = metingen.get(i).getGemWindsnelheid();
				int maxWindsnelheid = metingen.get(i).getMaxWindsnelheid();
				int minWindsnelheid = metingen.get(i).getMinWindsnelheid();
				int gemTemperatuur = metingen.get(i).getGemTemperatuur();
				int maxTemperatuur = metingen.get(i).getMaxTemperatuur();
				int minTemperatuur = metingen.get(i).getMinTemperatuur();
				int minTemperatuur10 = metingen.get(i).getMinTemperatuur10();
				int gemLuchtdruk = metingen.get(i).getGemLuchtdruk();
				int maxLuchtdruk = metingen.get(i).getMaxLuchtdruk();
				int minLuchtdruk = metingen.get(i).getMinLuchtdruk();
				int duurNeerslag = metingen.get(i).getDuurNeerslag();
				int somNeerslag = metingen.get(i).getSomNeerslag();

				prst.setInt(1, station_id);
				prst.setDate(2, datum);
				prst.setInt(3, windrichting);
				prst.setInt(4, gemWindsnelheid);
				prst.setInt(5, maxWindsnelheid);
				prst.setInt(6, minWindsnelheid);
				prst.setInt(7, gemTemperatuur);
				prst.setInt(8, maxTemperatuur);
				prst.setInt(9, minTemperatuur);
				prst.setInt(10, minTemperatuur10);
				prst.setInt(11, gemLuchtdruk);
				prst.setInt(12, maxLuchtdruk);
				prst.setInt(13, minLuchtdruk);
				prst.setInt(14, duurNeerslag);
				prst.setInt(15, somNeerslag);
				prst.execute();

				conn.commit();
			}
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			DBManager.getInstance().close();
		}	
	}

	private void postgresqlInsert(ArrayList<Meting> metingen) {

	}

	private void derbyInsert(ArrayList<Meting> metingen) {

	}

}
